﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Aspose.Cells;
using System.Data;
using System.Collections;
using System.Drawing;
namespace lcs.Web
{
    public class OutExcel
    {
        /// <summary>
        /// 利用Aspose.Cells对DataTable数据生成Excel文件
        /// </summary>
        /// <returns>是否成功</returns>
        /// <param name="response">包含存储路径，直接用Response即可</param>
        /// <param name="dt">数据体</param>
        /// <param name="FileName">文件名</param>
        /// <param name="SheetName">sheet名</param>
        /// <param name="Title">表头</param>
        /// <param name="ColTitle">列标题，字符串数组</param>
        /// <param name="ColName">列名，字符串数组</param>
        /// <param name="ColWidth">列宽，整数数组</param>
        /// <param name="ColStyle">列样式，整数数组，1=居中文本、2=特殊字体Georgia、3=居左文本、4=整数(x)、5=2位小数(x,xxx.xx)、6=日期(yyyy-M-d不带时间)、7=百分数(0.123=12.3%)</param>
        /// <param name="ColTitleRow">列标题的开始行号，有title的设为1即可</param>
        /// <param name="err">返回的错误信息</param>
        public static bool ExportToExcel(System.Web.HttpResponse response, DataTable dt, string outfile, ref string err)
        {
            try
            {
                

                string path = System.Web.HttpContext.Current.Server.MapPath("~");
                path = path.Substring(0, path.LastIndexOf("\\"));
                path += @"\template\pf.xls";
                Workbook workbook = new Workbook(path);
                //workbook.Open(path);
                //打开sheet
                //workbook.Worksheets.Clear();
                Worksheet worksheet = workbook.Worksheets[0];

                Cells cells = worksheet.Cells;
               
                int currentRow = 6;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    decimal temp = 0;
                    cells[currentRow, 0].PutValue((i + 1).ToString());
                    cells[currentRow,  2].PutValue(dt.Rows[i]["sname"].ToString());
                    cells[currentRow, 6].PutValue(dt.Rows[i]["material"].ToString());
                    cells[currentRow, 7].PutValue(dt.Rows[i]["yls"].ToString());
                    cells[currentRow, 8].PutValue(dt.Rows[i]["yle"].ToString());
                    cells[currentRow,  9].PutValue(dt.Rows[i]["scs"].ToString());
                    cells[currentRow, 10].PutValue(dt.Rows[i]["sce"].ToString());
                    cells[currentRow, 11].PutValue(dt.Rows[i]["scs"].ToString());
                    cells[currentRow, 12].PutValue(dt.Rows[i]["scwte1"].ToString());
                    cells[currentRow, 13].PutValue(dt.Rows[i]["zzs"].ToString());
                    cells[currentRow, 14].PutValue(dt.Rows[i]["zze"].ToString());
                    cells[currentRow, 15].PutValue(dt.Rows[i]["shs"].ToString());
                    cells[currentRow, 16].PutValue(dt.Rows[i]["she"].ToString());
                    cells[currentRow, 17].PutValue(dt.Rows[i]["dhs"].ToString());
                    cells[currentRow, 18].PutValue(dt.Rows[i]["dhe"].ToString());
                    cells[currentRow, 19].PutValue(dt.Rows[i]["azs"].ToString());
                    cells[currentRow, 20].PutValue(dt.Rows[i]["aze"].ToString());
                    decimal.TryParse(dt.Rows[i]["azsum"].ToString(), out temp);
                    cells[currentRow, 21].PutValue(temp.ToString("0.##"));
                    cells[currentRow, 22].PutValue(dt.Rows[i]["yxgzs"].ToString());
                    cells[currentRow, 23].PutValue(dt.Rows[i]["yxgze"].ToString());
                    cells[currentRow, 24].PutValue(dt.Rows[i]["yxbfs"].ToString());
                    cells[currentRow, 25].PutValue(dt.Rows[i]["yxbfe"].ToString());
                    
                    decimal.TryParse(dt.Rows[i]["yxsum"].ToString(), out temp);
                    cells[currentRow, 26].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["ylmark"].ToString(), out temp);
                    cells[currentRow, 27].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["scmark"].ToString(), out temp);
                    cells[currentRow, 28].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["zzmark"].ToString(), out temp);
                    cells[currentRow, 29].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["shmark"].ToString(), out temp);
                    cells[currentRow, 30].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["dhmark"].ToString(), out temp);
                    cells[currentRow, 31].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["azmark"].ToString(), out temp);
                    cells[currentRow, 32].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["azsum"].ToString(), out temp);
                    cells[currentRow, 33].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["yxzlmark"].ToString(), out temp);
                    cells[currentRow, 34].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["yxsum"].ToString(), out temp);
                    cells[currentRow, 35].PutValue(temp.ToString("0.##"));
                    decimal.TryParse(dt.Rows[i]["pjsum"].ToString(), out temp);
                    cells[currentRow, 36].PutValue(temp.ToString("0.##"));
                    currentRow++;

                }
                
                workbook.Save(response, outfile, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
                //workbook.Save(outfile, FileFormatType.Default, SaveType.OpenInExcel, response);
                return true;
            }
            catch (Exception ex)
            {
                err = ex.Message;
                return false;
            }
        }//end ExportToExcel()

    }
}